package com.design3.graduatethesissys.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.design3.graduatethesissys.domain.Regist;
import com.design3.graduatethesissys.domain.Teacher;

@Mapper
public interface TeacherMapper {
	
	@Update("update teacher set teh_name = #{teh_name}, teh_sex = #{teh_sex}, teh_phone = #{teh_phone}, teh_email = #{teh_email} where teh_id = #{teh_id}")
	void updateTeacher(Teacher teacher);
	
	@Select("select * from teacher where teh_id = #{teh_id}")
	List<Map<String, Object>> selectTeacher(String teh_id);
	
	@Select("SELECT\r\n" + 
			"	stu.stu_name,\r\n" + 
			"	stu.stu_phone,\r\n" + 
			"	stu.stu_mail,\r\n" + 
			"	stu.stu_id,\r\n" + 
			"	stu.stu_status \r\n" + 
			"FROM\r\n" + 
			"	teacher_student_subject tss\r\n" + 
			"	LEFT JOIN student stu ON stu.stu_id = tss.stu_id\r\n" + 
			"WHERE\r\n" + 
			"	tss.teh_id = #{teh_id}")
	List<Map<String, Object>> selectStudent(String teh_id);

	@Insert("insert into teacher (teh_id,teh_name,teh_sex,teh_phone,teh_email) values(#{teh_id},#{teh_name},#{teh_sex},#{teh_phone},#{teh_email})")
	void InsertStudent(Regist regist);
	
	@Select("SELECT\r\n" + 
			"	tss.stu_id as stu_id,\r\n" + 
			"	stu.stu_name as stu_name,\r\n" + 
			"   stu.stu_mail as stu_mail,\r\n"+
			"   stu.stu_phone as stu_phone,\r\n"+
			"	(select count(1) from message where tss.stu_id=message.from_id and message.mes_status ='zt01') as count\r\n" + 
			"FROM\r\n" + 
			"	teacher_student_subject tss\r\n" + 
			"	left join student stu on stu.stu_id=tss.stu_id\r\n" + 
			"WHERE\r\n" + 
			"	tss.teh_id = #{teh_id}")
	List<Map<String, Object>> selectMesCount(String teh_id);
	
	@Select("SELECT\r\n" + 
			"	sbj_id,\r\n" + 
			"	sbj_name,\r\n" + 
			"	sbj_describe,\r\n" + 
			"	teh_id,\r\n" + 
			"CASE\r\n" + 
			"	sbj_status \r\n" + 
			"	WHEN 'ct01' THEN\r\n" + 
			"	'暂时保存' \r\n" + 
			"	WHEN 'ct02' THEN\r\n" + 
			"	'待审核' \r\n" + 
			"	WHEN 'ct03' THEN\r\n" + 
			"	'已审核未被选' \r\n" + 
			"	WHEN 'ct04' THEN\r\n" + 
			"	'已被选' \r\n" + 
			"	WHEN 'ct05' THEN\r\n" + 
			"	'审核未通过' \r\n" + 
			"	END AS sbj_status \r\n" + 
			"FROM\r\n" + 
			"	`subject` \r\n" + 
			"WHERE\r\n" + 
			"	teh_id = #{teh_id}")
	List<Map<String, Object>> selectAllSbj(String teh_id);
}
